"""
数据验证和错误处理工具

根据设计文档实现Excel数据的完整性和有效性验证功能，
支持project_info、project_roles、phases_detail的验证。
"""

from typing import List, Dict, Any, Optional, Set, Tuple
from datetime import datetime, date
import logging
import re
import math
from pathlib import Path


class ValidationError(Exception):
    """数据验证错误异常"""
    def __init__(self, message: str, field: str = None, value: Any = None):
        super().__init__(message)
        self.field = field
        self.value = value
        self.message = message


class BusinessLogicError(Exception):
    """业务逻辑错误异常"""
    def __init__(self, message: str, code: str = None, suggestions: List[str] = None):
        super().__init__(message)
        self.code = code
        self.suggestions = suggestions or []
        self.message = message


class ConfigurationError(Exception):
    """配置错误异常"""
    def __init__(self, message: str, config_file: str = None):
        super().__init__(message)
        self.config_file = config_file
        self.message = message


class DataValidator:
    """Excel资源计划数据验证器类

    根据设计文档实现对Excel输入数据的全面验证：
    - project_info表验证
    - project_roles表验证
    - phases_detail表验证
    - 业务逻辑验证（角色引用、成本一致性等）
    - Excel公式错误检测
    """

    def __init__(self, strict_mode: bool = True):
        """
        初始化验证器

        Args:
            strict_mode: 严格模式，警告也会被视为错误
        """
        self.logger = logging.getLogger(__name__)
        self.strict_mode = strict_mode
        self.errors = []  # 严重错误列表
        self.warnings = []  # 警告列表
        self.suggestions = []  # 修复建议列表

    def validate_project_info(self, project_info: Dict[str, Any]) -> List[str]:
        """
        验证project_info表数据

        验证规则：
        - project_name: 必填，最大100字符，不能为空
        - start_year_month: 必填，格式YYYY-MM
        - end_year_month: 必填，格式YYYY-MM，必须晚于开始日期
        - 项目持续时间不能超过36个月

        Args:
            project_info: 项目信息字典

        Returns:
            验证错误信息列表
        """
        errors = []

        # 1. 检查必填字段
        required_fields = ['project_name', 'start_year_month', 'end_year_month']
        for field in required_fields:
            if field not in project_info or not project_info[field]:
                errors.append(f"project_info表缺少必填字段: {field}")
                continue

        # 如果缺少必填字段，直接返回
        if errors:
            return errors

        # 2. 验证项目名称
        project_name = str(project_info['project_name']).strip()
        if not project_name:
            errors.append("项目名称不能为空")
        elif len(project_name) > 100:
            errors.append(f"项目名称过长（{len(project_name)}字符），最大100字符")

        # 3. 验证日期格式和逻辑
        start_month = project_info['start_year_month']
        end_month = project_info['end_year_month']

        # 验证日期格式
        if not self._is_valid_year_month(start_month):
            errors.append(f"开始年月格式错误: {start_month}，应为YYYY-MM格式")
        if not self._is_valid_year_month(end_month):
            errors.append(f"结束年月格式错误: {end_month}，应为YYYY-MM格式")

        # 如果日期格式正确，检查逻辑
        if not errors or all('格式错误' not in err for err in errors[-2:]):
            try:
                start_date = self._parse_year_month(start_month)
                end_date = self._parse_year_month(end_month)

                if start_date >= end_date:
                    errors.append(f"项目开始时间({start_month})不能晚于或等于结束时间({end_month})")

                # 检查项目持续时间
                duration_months = self._calculate_month_difference(start_date, end_date)
                if duration_months > 36:
                    errors.append(f"项目持续时间({duration_months}个月)超过最大限制(36个月)")
                elif duration_months < 1:
                    errors.append(f"项目持续时间过短({duration_months}个月)，至少需要1个月")

            except Exception as e:
                errors.append(f"日期解析错误: {str(e)}")

        return errors

    def validate_project_roles(self, project_roles: List[Dict[str, Any]]) -> List[str]:
        """
        验证project_roles表数据

        验证规则：
        - role: 必填，最大20字符，允许的角色值
        - unit_price: 必填，数值类型，范围10万-500万日元
        - total_cost: 公式字段，检查是否有公式错误

        Args:
            project_roles: 项目角色列表

        Returns:
            验证错误信息列表
        """
        errors = []

        if not project_roles:
            errors.append("project_roles表不能为空")
            return errors

        # 允许的角色值
        allowed_roles = {"PM", "TL", "SSE", "SE", "PG", "BA", "テスター", "UI", "DBA"}
        role_names = []

        for i, role_data in enumerate(project_roles, 1):
            row_prefix = f"project_roles表第{i}行"

            # 1. 检查必填字段
            if 'role' not in role_data or not role_data['role']:
                errors.append(f"{row_prefix}: 角色名称不能为空")
                continue

            if 'unit_price' not in role_data:
                errors.append(f"{row_prefix}: 缺少单价字段")
                continue

            # 2. 验证角色名称
            role_name = str(role_data['role']).strip()
            if not role_name:
                errors.append(f"{row_prefix}: 角色名称不能为空")
            elif len(role_name) > 20:
                errors.append(f"{row_prefix}: 角色名称过长({len(role_name)}字符)，最大20字符")
            elif role_name not in allowed_roles:
                errors.append(f"{row_prefix}: 不支持的角色'{role_name}'，允许的角色: {', '.join(sorted(allowed_roles))}")
            else:
                role_names.append(role_name)

            # 3. 验证单价
            try:
                unit_price = float(role_data['unit_price'])
                if unit_price < 100000:
                    errors.append(f"{row_prefix}: 单价过低({unit_price:,.0f}日元)，最小10万日元")
                elif unit_price > 5000000:
                    errors.append(f"{row_prefix}: 单价过高({unit_price:,.0f}日元)，最大500万日元")
            except (ValueError, TypeError):
                errors.append(f"{row_prefix}: 单价必须是数值类型")

            # 4. 检查total_cost公式
            if 'total_cost' in role_data:
                total_cost = role_data['total_cost']
                if isinstance(total_cost, str) and total_cost.startswith('#'):
                    errors.append(f"{row_prefix}: total_cost公式错误 - {total_cost}")

        # 5. 检查角色名称重复
        duplicates = self._find_duplicates(role_names)
        if duplicates:
            errors.append(f"project_roles表中角色名称重复: {', '.join(duplicates)}")

        return errors

    def validate_phases_detail(self, phases_detail: List[Dict[str, Any]],
                             project_info: Dict[str, Any],
                             project_roles: List[Dict[str, Any]]) -> List[str]:
        """
        验证phases_detail表数据

        验证规则：
        - phase_id: 必填，最大20字符，格式P\d+
        - phase_name: 必填，最大50字符
        - start_year_month/end_year_month: 必填，格式YYYY-MM，在项目时间范围内
        - role: 必填，必须在project_roles中定义
        - total_cost: 必填，正数，最大5000万日元
        - unit_price: 公式字段，检查VLOOKUP错误
        - person_months: 公式字段，检查计算一致性

        Args:
            phases_detail: 阶段详细信息列表
            project_info: 项目基本信息
            project_roles: 项目角色信息

        Returns:
            验证错误信息列表
        """
        errors = []

        if not phases_detail:
            errors.append("phases_detail表不能为空")
            return errors

        # 获取项目时间范围
        try:
            project_start = self._parse_year_month(project_info['start_year_month'])
            project_end = self._parse_year_month(project_info['end_year_month'])
        except:
            errors.append("无法解析项目时间范围，请先修复project_info表")
            return errors

        # 获取定义的角色
        defined_roles = {role['role'] for role in project_roles if 'role' in role}
        phase_ids = []

        for i, phase in enumerate(phases_detail, 1):
            row_prefix = f"phases_detail表第{i}行"

            # 1. 检查必填字段
            required_fields = ['phase_id', 'phase_name', 'start_year_month',
                             'end_year_month', 'role', 'total_cost']
            for field in required_fields:
                if field not in phase or phase[field] is None or phase[field] == '':
                    errors.append(f"{row_prefix}: 缺少必填字段 {field}")
                    continue

            # 如果缺少必填字段，跳过此行的其他验证
            if any(field not in phase or phase[field] is None or phase[field] == ''
                   for field in required_fields):
                continue

            # 2. 验证phase_id
            phase_id = str(phase['phase_id']).strip()
            if not re.match(r'^P\d+$', phase_id):
                errors.append(f"{row_prefix}: phase_id格式错误'{phase_id}'，应为P001、P002等格式")
            elif len(phase_id) > 20:
                errors.append(f"{row_prefix}: phase_id过长({len(phase_id)}字符)，最大20字符")
            else:
                phase_ids.append(phase_id)

            # 3. 验证phase_name
            phase_name = str(phase['phase_name']).strip()
            if not phase_name:
                errors.append(f"{row_prefix}: 阶段名称不能为空")
            elif len(phase_name) > 50:
                errors.append(f"{row_prefix}: 阶段名称过长({len(phase_name)}字符)，最大50字符")

            # 4. 验证日期
            start_month = phase['start_year_month']
            end_month = phase['end_year_month']

            if not self._is_valid_year_month(start_month):
                errors.append(f"{row_prefix}: 开始年月格式错误'{start_month}'，应为YYYY-MM格式")
            if not self._is_valid_year_month(end_month):
                errors.append(f"{row_prefix}: 结束年月格式错误'{end_month}'，应为YYYY-MM格式")
            else:
                try:
                    phase_start = self._parse_year_month(start_month)
                    phase_end = self._parse_year_month(end_month)

                    # 检查阶段日期逻辑
                    if phase_start > phase_end:
                        errors.append(f"{row_prefix}: 开始时间不能晚于结束时间")

                    # 检查是否在项目时间范围内
                    if phase_start < project_start:
                        errors.append(f"{row_prefix}: 阶段开始时间早于项目开始时间")
                    if phase_end > project_end:
                        errors.append(f"{row_prefix}: 阶段结束时间晚于项目结束时间")

                except Exception as e:
                    errors.append(f"{row_prefix}: 日期解析错误 - {str(e)}")

            # 5. 验证角色引用
            role = str(phase['role']).strip()
            if role not in defined_roles:
                errors.append(f"{row_prefix}: 角色'{role}'未在project_roles表中定义")

            # 6. 验证total_cost
            try:
                total_cost = float(phase['total_cost'])
                if total_cost < 10000:
                    errors.append(f"{row_prefix}: 总成本过低({total_cost:,.0f}日元)，最小1万日元")
                elif total_cost > 50000000:
                    errors.append(f"{row_prefix}: 总成本过高({total_cost:,.0f}日元)，最大5000万日元")
            except (ValueError, TypeError):
                errors.append(f"{row_prefix}: total_cost必须是数值类型")

            # 7. 检查公式字段
            if 'unit_price' in phase:
                unit_price = phase['unit_price']
                if isinstance(unit_price, str) and unit_price.startswith('#'):
                    errors.append(f"{row_prefix}: unit_price公式错误 - {unit_price}")

            if 'person_months' in phase:
                person_months = phase['person_months']
                if isinstance(person_months, str) and person_months.startswith('#'):
                    errors.append(f"{row_prefix}: person_months公式错误 - {person_months}")

        # 8. 检查phase_id重复
        duplicates = self._find_duplicates(phase_ids)
        if duplicates:
            errors.append(f"phases_detail表中phase_id重复: {', '.join(duplicates)}")

        return errors

    def validate_business_logic(self, project_info: Dict[str, Any],
                              project_roles: List[Dict[str, Any]],
                              phases_detail: List[Dict[str, Any]]) -> Tuple[List[str], List[str]]:
        """
        业务逻辑验证

        验证：
        - 角色引用完整性
        - 成本一致性（person_months * unit_price = total_cost）
        - PM角色覆盖度
        - 阶段时间合理性

        Args:
            project_info: 项目基本信息
            project_roles: 项目角色信息
            phases_detail: 阶段详细信息

        Returns:
            (errors, warnings): 错误和警告列表
        """
        errors = []
        warnings = []

        # 1. 角色引用完整性检查
        defined_roles = {role['role'] for role in project_roles if 'role' in role}
        used_roles = {phase['role'] for phase in phases_detail if 'role' in phase}

        # 检查未定义的角色
        missing_roles = used_roles - defined_roles
        if missing_roles:
            errors.append(f"phases_detail中使用了未定义的角色: {', '.join(sorted(missing_roles))}")

        # 检查未使用的角色
        unused_roles = defined_roles - used_roles
        if unused_roles:
            warnings.append(f"project_roles中定义但未使用的角色: {', '.join(sorted(unused_roles))}")

        # 2. 成本一致性检查
        role_price_map = {role['role']: role.get('unit_price')
                         for role in project_roles if 'role' in role}

        for i, phase in enumerate(phases_detail, 1):
            if not all(key in phase for key in ['role', 'total_cost']):
                continue

            role = phase['role']
            total_cost = phase.get('total_cost')
            unit_price = phase.get('unit_price') or role_price_map.get(role)
            person_months = phase.get('person_months')

            # 如果有完整的数据，检查一致性
            if all(x is not None for x in [total_cost, unit_price, person_months]):
                try:
                    calculated_cost = float(person_months) * float(unit_price)
                    actual_cost = float(total_cost)

                    # 允许1000日元的误差
                    if abs(calculated_cost - actual_cost) > 1000:
                        errors.append(
                            f"阶段{phase.get('phase_id', i)}成本计算不一致: "
                            f"应为{calculated_cost:,.0f}日元，实际为{actual_cost:,.0f}日元"
                        )
                except (ValueError, TypeError):
                    continue

        # 3. PM角色覆盖度检查
        pm_phases = [p for p in phases_detail if p.get('role') == 'PM']
        if pm_phases:
            pm_coverage = self._check_pm_coverage(pm_phases, project_info)
            if not pm_coverage:
                warnings.append("PM角色未完全覆盖项目周期，建议检查项目管理连续性")
        else:
            warnings.append("项目中未配置PM角色，建议添加项目管理人员")

        # 4. 阶段时间重叠检查
        overlap_warnings = self._check_phase_overlaps(phases_detail)
        warnings.extend(overlap_warnings)

        return errors, warnings

    def validate_excel_formulas(self, workbook) -> List[str]:
        """
        检测Excel公式错误

        检查常见的公式错误：
        - #N/A: VLOOKUP找不到匹配值
        - #DIV/0!: 除零错误
        - #VALUE!: 数值错误
        - #REF!: 引用错误

        Args:
            workbook: openpyxl工作簿对象

        Returns:
            公式错误列表
        """
        errors = []

        try:
            for sheet_name in workbook.sheetnames:
                sheet = workbook[sheet_name]

                for row in sheet.iter_rows():
                    for cell in row:
                        if cell.value and isinstance(cell.value, str):
                            cell_value = str(cell.value)

                            # 检查公式错误
                            if cell_value.startswith('#'):
                                error_location = f"{sheet_name}!{cell.coordinate}"

                                if cell_value == '#N/A':
                                    errors.append(
                                        f"VLOOKUP错误 {error_location}: 找不到匹配值，"
                                        "请检查角色名称是否与project_roles表完全一致"
                                    )
                                elif cell_value == '#DIV/0!':
                                    errors.append(
                                        f"除零错误 {error_location}: 请检查单价是否为零"
                                    )
                                elif cell_value == '#VALUE!':
                                    errors.append(
                                        f"数值错误 {error_location}: 请检查数据格式是否正确"
                                    )
                                elif cell_value == '#REF!':
                                    errors.append(
                                        f"引用错误 {error_location}: 公式引用了无效的单元格"
                                    )
                                else:
                                    errors.append(
                                        f"公式错误 {error_location}: {cell_value}"
                                    )

        except Exception as e:
            errors.append(f"公式错误检查失败: {str(e)}")

        return errors

    def handle_edge_cases(self, project_info: Dict[str, Any],
                         project_roles: List[Dict[str, Any]],
                         phases_detail: List[Dict[str, Any]]) -> Tuple[Dict[str, Any], List[Dict[str, Any]], List[Dict[str, Any]]]:
        """
        处理边界情况和数据修复

        Args:
            project_info: 项目基本信息
            project_roles: 项目角色列表
            phases_detail: 阶段详细信息列表

        Returns:
            (修复后的数据, 修复记录, 警告列表)
        """
        fixed_project_info = project_info.copy()
        fixed_project_roles = []
        fixed_phases_detail = []
        fixes = []
        edge_warnings = []

        # 1. 处理project_info边界情况
        if 'project_name' in fixed_project_info:
            name = str(fixed_project_info['project_name']).strip()
            if not name and not self.strict_mode:
                fixed_project_info['project_name'] = "未命名项目"
                fixes.append("项目名称为空，已设置为默认值'未命名项目'")

        # 2. 处理project_roles边界情况
        for i, role in enumerate(project_roles):
            fixed_role = role.copy()

            # 处理角色名称
            if 'role' in fixed_role:
                role_name = str(fixed_role['role']).strip()
                if not role_name and not self.strict_mode:
                    continue  # 跳过空角色行

            # 处理单价边界值
            if 'unit_price' in fixed_role:
                try:
                    price = float(fixed_role['unit_price'])
                    if price <= 0 and not self.strict_mode:
                        fixed_role['unit_price'] = 100000  # 设置最小默认值
                        fixes.append(f"角色{role_name}的单价无效，已设置为默认值100,000日元")
                    elif price < 100000 and not self.strict_mode:
                        edge_warnings.append(f"角色{role_name}的单价({price:,.0f}日元)较低，请确认是否正确")
                    elif price > 5000000 and not self.strict_mode:
                        edge_warnings.append(f"角色{role_name}的单价({price:,.0f}日元)较高，请确认是否正确")
                except (ValueError, TypeError):
                    if not self.strict_mode:
                        fixed_role['unit_price'] = 300000  # 设置默认值
                        fixes.append(f"角色{role_name}的单价格式错误，已设置为默认值300,000日元")

            fixed_project_roles.append(fixed_role)

        # 3. 处理phases_detail边界情况
        for i, phase in enumerate(phases_detail):
            fixed_phase = phase.copy()

            # 处理phase_id
            if 'phase_id' in fixed_phase:
                phase_id = str(fixed_phase['phase_id']).strip()
                if not phase_id and not self.strict_mode:
                    fixed_phase['phase_id'] = f"P{i+1:03d}"
                    fixes.append(f"第{i+1}行phase_id为空，已自动生成ID: {fixed_phase['phase_id']}")

            # 处理阶段名称
            if 'phase_name' in fixed_phase:
                phase_name = str(fixed_phase['phase_name']).strip()
                if not phase_name and not self.strict_mode:
                    fixed_phase['phase_name'] = f"阶段{i+1}"
                    fixes.append(f"第{i+1}行阶段名称为空，已设置为默认值'阶段{i+1}'")

            # 处理非常小的总成本
            if 'total_cost' in fixed_phase:
                try:
                    cost = float(fixed_phase['total_cost'])
                    if 0 < cost < 10000 and not self.strict_mode:
                        edge_warnings.append(f"阶段{fixed_phase.get('phase_id', i+1)}的总成本({cost:,.0f}日元)较低，请确认是否正确")
                except (ValueError, TypeError):
                    pass

            # 处理人月数边界情况
            if 'person_months' in fixed_phase:
                try:
                    person_months = float(fixed_phase['person_months'])
                    if 0 < person_months < 0.1 and not self.strict_mode:
                        edge_warnings.append(f"阶段{fixed_phase.get('phase_id', i+1)}的人月数({person_months:.2f})较小，可能需要调整")
                except (ValueError, TypeError):
                    pass

            # 处理日期格式问题
            for date_field in ['start_year_month', 'end_year_month']:
                if date_field in fixed_phase:
                    date_value = str(fixed_phase[date_field]).strip()
                    # 尝试修复常见的日期格式问题
                    if date_value and not self.strict_mode:
                        # 处理 YYYY/MM 格式
                        if '/' in date_value and '-' not in date_value:
                            fixed_date = date_value.replace('/', '-')
                            if self._is_valid_year_month(fixed_date):
                                fixed_phase[date_field] = fixed_date
                                fixes.append(f"阶段{fixed_phase.get('phase_id', i+1)}的{date_field}格式已修正: {date_value} -> {fixed_date}")

            fixed_phases_detail.append(fixed_phase)

        return (
            {
                'project_info': fixed_project_info,
                'project_roles': fixed_project_roles,
                'phases_detail': fixed_phases_detail
            },
            fixes,
            edge_warnings
        )

    def validate_all_excel_data(self, project_info: Dict[str, Any],
                               project_roles: List[Dict[str, Any]],
                               phases_detail: List[Dict[str, Any]],
                               workbook=None, auto_fix: bool = False) -> Dict[str, Any]:
        """
        执行所有Excel数据验证

        Args:
            project_info: 项目基本信息
            project_roles: 项目角色列表
            phases_detail: 阶段详细信息列表
            workbook: Excel工作簿对象（可选，用于公式错误检查）
            auto_fix: 是否自动修复边界情况（仅在非严格模式下生效）

        Returns:
            验证结果字典，包含errors、warnings、suggestions、fixes

        Raises:
            ValidationError: 存在严重验证错误且为严格模式
        """
        self.errors = []
        self.warnings = []
        self.suggestions = []
        fixes = []
        fixed_data = None

        # 0. 边界情况处理（仅在非严格模式且启用auto_fix时）
        if auto_fix and not self.strict_mode:
            fixed_data, fixes, edge_warnings = self.handle_edge_cases(
                project_info, project_roles, phases_detail
            )
            self.warnings.extend(edge_warnings)

            # 使用修复后的数据进行验证
            project_info = fixed_data['project_info']
            project_roles = fixed_data['project_roles']
            phases_detail = fixed_data['phases_detail']

        # 1. 基础数据验证
        self.errors.extend(self.validate_project_info(project_info))
        self.errors.extend(self.validate_project_roles(project_roles))
        self.errors.extend(self.validate_phases_detail(phases_detail, project_info, project_roles))

        # 2. 业务逻辑验证
        if not self.errors:  # 只有基础验证通过才进行业务逻辑验证
            business_errors, business_warnings = self.validate_business_logic(
                project_info, project_roles, phases_detail
            )
            self.errors.extend(business_errors)
            self.warnings.extend(business_warnings)

        # 3. Excel公式错误检查
        if workbook:
            formula_errors = self.validate_excel_formulas(workbook)
            self.errors.extend(formula_errors)

        # 4. 生成修复建议
        self.suggestions = self._generate_fix_suggestions(self.errors)

        # 5. 准备结果
        result = {
            'valid': len(self.errors) == 0 and (not self.strict_mode or len(self.warnings) == 0),
            'errors': self.errors,
            'warnings': self.warnings,
            'suggestions': self.suggestions,
            'fixes': fixes,
            'fixed_data': fixed_data,
            'total_issues': len(self.errors) + len(self.warnings),
            'mode': 'strict' if self.strict_mode else 'relaxed',
            'auto_fix_applied': auto_fix and not self.strict_mode and len(fixes) > 0
        }

        # 6. 记录日志
        if fixes:
            self.logger.info(f"已自动修复{len(fixes)}个边界情况问题")
        if self.errors:
            self.logger.error(f"数据验证发现{len(self.errors)}个错误")
        if self.warnings:
            self.logger.warning(f"数据验证发现{len(self.warnings)}个警告")

        # 7. 严格模式下抛出异常
        if self.strict_mode and (self.errors or self.warnings):
            total_issues = len(self.errors) + len(self.warnings)
            raise ValidationError(
                f"严格模式下发现{total_issues}个问题（{len(self.errors)}个错误，{len(self.warnings)}个警告）"
            )
        elif self.errors:
            # 在宽松模式下，如果有不可修复的错误才抛出异常
            if not auto_fix or len(self.errors) > 0:
                raise ValidationError(f"数据验证失败，发现{len(self.errors)}个错误")

        return result

    # ============= 私有辅助方法 =============

    def _is_valid_year_month(self, year_month: str) -> bool:
        """
        验证年月格式是否为YYYY-MM

        Args:
            year_month: 年月字符串

        Returns:
            是否为有效格式
        """
        if not isinstance(year_month, str):
            return False

        pattern = r'^\d{4}-\d{2}$'
        if not re.match(pattern, year_month):
            return False

        try:
            year, month = year_month.split('-')
            year = int(year)
            month = int(month)

            # 检查年份和月份范围
            if not (2020 <= year <= 2030):
                return False
            if not (1 <= month <= 12):
                return False

            return True
        except:
            return False

    def _parse_year_month(self, year_month: str) -> datetime:
        """
        解析年月字符串为datetime对象

        Args:
            year_month: 格式为YYYY-MM的字符串

        Returns:
            datetime对象

        Raises:
            ValueError: 格式错误
        """
        try:
            return datetime.strptime(year_month, '%Y-%m')
        except ValueError:
            raise ValueError(f"无效的年月格式: {year_month}，应为YYYY-MM格式")

    def _calculate_month_difference(self, start_date: datetime, end_date: datetime) -> int:
        """
        计算两个日期之间的月份差

        Args:
            start_date: 开始日期
            end_date: 结束日期

        Returns:
            月份差
        """
        return (end_date.year - start_date.year) * 12 + (end_date.month - start_date.month) + 1

    def _find_duplicates(self, items: List[str]) -> List[str]:
        """
        查找重复项

        Args:
            items: 字符串列表

        Returns:
            重复项列表
        """
        seen = set()
        duplicates = set()

        for item in items:
            if item in seen:
                duplicates.add(item)
            else:
                seen.add(item)

        return sorted(list(duplicates))

    def _check_pm_coverage(self, pm_phases: List[Dict[str, Any]],
                          project_info: Dict[str, Any]) -> bool:
        """
        检查PM角色是否覆盖整个项目周期

        Args:
            pm_phases: PM角色的阶段列表
            project_info: 项目基本信息

        Returns:
            是否完全覆盖
        """
        try:
            project_start = self._parse_year_month(project_info['start_year_month'])
            project_end = self._parse_year_month(project_info['end_year_month'])

            # 收集所有PM阶段的时间范围
            pm_periods = []
            for phase in pm_phases:
                if all(key in phase for key in ['start_year_month', 'end_year_month']):
                    start = self._parse_year_month(phase['start_year_month'])
                    end = self._parse_year_month(phase['end_year_month'])
                    pm_periods.append((start, end))

            if not pm_periods:
                return False

            # 检查是否覆盖项目开始和结束时间
            pm_start = min(period[0] for period in pm_periods)
            pm_end = max(period[1] for period in pm_periods)

            return pm_start <= project_start and pm_end >= project_end

        except Exception:
            return False

    def _check_phase_overlaps(self, phases_detail: List[Dict[str, Any]]) -> List[str]:
        """
        检查阶段时间重叠情况

        Args:
            phases_detail: 阶段详细信息列表

        Returns:
            重叠警告列表
        """
        warnings = []

        # 按角色分组检查重叠
        role_phases = {}
        for phase in phases_detail:
            if all(key in phase for key in ['role', 'start_year_month', 'end_year_month']):
                role = phase['role']
                if role not in role_phases:
                    role_phases[role] = []
                role_phases[role].append(phase)

        for role, phases in role_phases.items():
            if len(phases) <= 1:
                continue

            # 检查同角色的时间重叠
            for i, phase1 in enumerate(phases):
                for j, phase2 in enumerate(phases[i+1:], i+1):
                    try:
                        start1 = self._parse_year_month(phase1['start_year_month'])
                        end1 = self._parse_year_month(phase1['end_year_month'])
                        start2 = self._parse_year_month(phase2['start_year_month'])
                        end2 = self._parse_year_month(phase2['end_year_month'])

                        # 检查是否重叠
                        if not (end1 < start2 or end2 < start1):
                            warnings.append(
                                f"角色{role}在阶段{phase1.get('phase_id', '?')}和"
                                f"{phase2.get('phase_id', '?')}中存在时间重叠"
                            )
                    except Exception:
                        continue

        return warnings

    def _generate_fix_suggestions(self, errors: List[str]) -> List[str]:
        """
        根据错误类型生成修复建议

        Args:
            errors: 错误列表

        Returns:
            修复建议列表
        """
        suggestions = []

        for error in errors:
            if "未定义的角色" in error:
                suggestions.append("请在project_roles表中添加缺失的角色定义")
            elif "成本计算不一致" in error:
                suggestions.append("请检查total_cost是否等于person_months × unit_price")
            elif "日期范围错误" in error or "时间范围" in error:
                suggestions.append("请检查阶段日期是否在项目时间范围内，且开始时间早于结束时间")
            elif "VLOOKUP错误" in error:
                suggestions.append("请检查phases_detail中的角色名是否与project_roles中完全一致（注意大小写和空格）")
            elif "格式错误" in error:
                suggestions.append("请使用YYYY-MM格式输入年月，例如2024-01")
            elif "单价" in error and ("过低" in error or "过高" in error):
                suggestions.append("请检查单价设置是否合理（建议范围：10万-500万日元/月）")
            elif "重复" in error:
                suggestions.append("请确保ID和名称的唯一性，删除重复项")
            elif "公式错误" in error:
                suggestions.append("请检查Excel公式引用的单元格和工作表名称是否正确")

        # 去重
        return list(dict.fromkeys(suggestions))


def create_validator(strict_mode: bool = True) -> DataValidator:
    """
    创建数据验证器实例

    Args:
        strict_mode: 是否启用严格模式

    Returns:
        DataValidator实例
    """
    return DataValidator(strict_mode=strict_mode)


def validate_excel_data(project_info: Dict[str, Any],
                       project_roles: List[Dict[str, Any]],
                       phases_detail: List[Dict[str, Any]],
                       strict_mode: bool = True,
                       auto_fix: bool = False,
                       workbook=None) -> Dict[str, Any]:
    """
    便捷函数：验证Excel数据

    Args:
        project_info: 项目基本信息
        project_roles: 项目角色列表
        phases_detail: 阶段详细信息列表
        strict_mode: 是否启用严格模式
        auto_fix: 是否自动修复边界情况（仅在宽松模式下生效）
        workbook: Excel工作簿对象（可选）

    Returns:
        验证结果字典，包含：
        - valid: 是否验证通过
        - errors: 错误列表
        - warnings: 警告列表
        - suggestions: 修复建议列表
        - fixes: 自动修复记录（如果启用auto_fix）
        - fixed_data: 修复后的数据（如果启用auto_fix且有修复）
        - mode: 验证模式（'strict' 或 'relaxed'）
        - auto_fix_applied: 是否应用了自动修复
    """
    validator = create_validator(strict_mode)
    return validator.validate_all_excel_data(
        project_info, project_roles, phases_detail, workbook, auto_fix
    )